package com.zy.dao.impl;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.junit.Test;

import com.sun.org.apache.bcel.internal.generic.NEW;
import com.zy.dao.CustomerDao;
import com.zy.entity.Customer;
import com.zy.entity.CustomerEx;
import com.zy.entity.Dict;
import com.zy.utils.JDBCutil;

import sun.net.www.content.text.plain;

public class CustomerDaoImpl implements CustomerDao {

	@Override
	public List<Dict> getDict() {
		Connection con = null;
		String sql = "SELECT * from t_dict";

		try {
			// 从配置文件里面取值
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(con, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);
			// PreparedStatement ps=JDBCutil.getPS(con,
			// "jdbc:mysql://localhost:3306/crm", "root", "1234", sql);

			ResultSet rs = ps.executeQuery();

			// 创建集合
			ArrayList<Dict> arrayList = new ArrayList<>();
			// 填充集合
			while (rs.next()) {
				Dict dict = new Dict();
				dict.setDid(rs.getInt(1));
				dict.setDname(rs.getString(2));

				arrayList.add(dict);

			}
			// 返回list
			return arrayList;

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return null;
	}

	@Test
	public void show() throws Exception, Exception {// 测试成功！！！！
		// 从配置文件里面取值
		Properties properties = new Properties();

		properties.load(new FileInputStream("src/jdbc.txt"));

		String a = properties.getProperty("jdbc.url");

		System.out.println(a);

	}

	@Override
	public int getTotalCount() {
		try {
			// 从配置文件里面取值
			String sql = "SELECT COUNT(*) from t_customer";
			Connection con = null;
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(con, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);

			// 执行
			ResultSet rs = ps.executeQuery();

			int num = 0;
			while (rs.next()) {
				num = rs.getInt(1);
			}

			// 关闭资源
			JDBCutil.closeAll(rs, ps, con);

			return num;

		} catch (Exception e) {
			// TODO: handle exception
		}
		return 0;
	}

	@Override
	public List<Customer> getCustomers(int m, int n) {

		try {
			// 从配置文件里面取值
			String sql = "SELECT cid,custName,dname,custSource," + " custPhone,custMobile from t_customer"
					+ " INNER JOIN t_dict on custLevel=did" + " LIMIT ?,?";
			Connection con = null;
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(con, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);
			// 执行
			ps.setInt(1, m);
			ps.setInt(2, n);
			
			ResultSet rs = ps.executeQuery();
			List<Customer> list=new ArrayList<>();
			
			while(rs.next()){
				Customer customer = new Customer();
				customer.setCid(rs.getInt(1));
				customer.setCustName(rs.getString(2));
				//--------------------------------------------
				customer.getDict().setDname(rs.getString(3));
				//----------------------------------------------
				customer.setCustSource(rs.getString(4));
				customer.setCustPhone(rs.getString(5));
				customer.setCustMobile(rs.getString(6));
				//装入集合
				list.add(customer);
				
			}
			//关闭资源
			JDBCutil.closeAll(rs, ps, con);
			//返回数据
			return list;

		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}

	@Override
	public Customer getCustomer(int cid) {
		try {
			String sql = "select * from t_customer where cid=?";
			Connection con = null;
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(con, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);

			// 执行
			ps.setInt(1, cid);
			
			ResultSet rs = ps.executeQuery();
			Customer customer = new Customer();
			

			while (rs.next()) {
				customer.setCid(rs.getInt(1));
				customer.setCustName(rs.getString(2));
				customer.setCustSource(rs.getString(3));
				customer.setCustPhone(rs.getString(4));
				customer.setCustMobile(rs.getString(5));
				customer.getDict().setDid(rs.getInt(6));
			}

			// 关闭资源
			JDBCutil.closeAll(rs, ps, con);
			return customer;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public void updateCustomer(Customer customer) {
		try {
			String sql = "update t_customer set custName=?,custSource=?,custPhone=?,custMobile=?,custLevel=? where cid=?";
			Connection con = null;
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(con, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);

			// 执行
			ps.setString(1, customer.getCustName());
			ps.setString(2, customer.getCustSource());
			ps.setString(3, customer.getCustPhone());
			ps.setString(4, customer.getCustMobile());
			ps.setInt(5, customer.getDict().getDid());
			ps.setInt(6, customer.getCid());
			
			ps.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

	@Override
	public void insertCustomer(Customer customer) {
		try {
			String sql = "insert into t_customer values(null,?,?,?,?,?)";
			Connection con = null;
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(con, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);

			// 执行
			ps.setString(1, customer.getCustName());
			ps.setString(2, customer.getCustSource());
			ps.setString(3, customer.getCustPhone());
			ps.setString(4, customer.getCustMobile());
			ps.setInt(5, customer.getDict().getDid());
			
			
			ps.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

	@Override
	public void deleteCustomer(int cid) {
		try {
			String sql = "delete from t_customer where cid=?";
			Connection con = null;
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(con, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);

			// 执行
			ps.setInt(1, cid);
			ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

	@Override
	public List<Customer> selectAllCustomer() {
		try {
			String sql = "SELECT cid,custName,dname,custSource," + " custPhone,custMobile from t_customer"
					+ " INNER JOIN t_dict on custLevel=did";
			Connection connection = null;
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(connection, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);
			
			ResultSet rs = ps.executeQuery();
			List<Customer> customers = new ArrayList<>();
			
			while(rs.next()){
				Customer customer = new Customer();
				customer.setCid(rs.getInt(1));
				customer.setCustName(rs.getString(2));
				//--------------------------------------------
				customer.getDict().setDname(rs.getString(3));
				//----------------------------------------------
				customer.setCustSource(rs.getString(4));
				customer.setCustPhone(rs.getString(5));
				customer.setCustMobile(rs.getString(6));
				//装入集合
				customers.add(customer);
				
			}
			//关闭资源
			JDBCutil.closeAll(rs, ps, connection);
			//返回数据
			return customers;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<Customer> queryByName(String name) {
		try {
			String str = "'%"+name+"%'";
			String sql = "SELECT cid,custName,dname,custSource," + " custPhone,custMobile from t_customer"
					+ " INNER JOIN t_dict on custLevel=did"+ " where custName like "+str;
			Connection connection = null;
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(connection, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);
			
			ResultSet rs = ps.executeQuery();
			List<Customer> customers = new ArrayList<>();
			
			while (rs.next()) {
				Customer customer = new Customer();
				customer.setCid(rs.getInt(1));
				customer.setCustName(rs.getString(2));
				customer.getDict().setDname(rs.getString(3));
				customer.setCustSource(rs.getString(4));
				customer.setCustPhone(rs.getString(5));
				customer.setCustMobile(rs.getString(6));
				
				customers.add(customer);
			}

			// 关闭资源
			JDBCutil.closeAll(rs, ps, connection);
			return customers;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<Customer> getCustomernl(String custname, int custlevel) {
		try {
			String str = "'%"+custname+"%'";
			String sql = "SELECT cid,custName,dname,custSource," + " custPhone,custMobile from t_customer"
					+ " INNER JOIN t_dict on custLevel=did"+ " where custName like "+str+"and custLevel=?";
			Connection connection = null;
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(connection, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);
			
			ps.setInt(1, custlevel);
			ResultSet rs = ps.executeQuery();
			List<Customer> customers = new ArrayList<>();
			
			while (rs.next()) {
				Customer customer = new Customer();
				customer.setCid(rs.getInt(1));
				customer.setCustName(rs.getString(2));
				customer.getDict().setDname(rs.getString(3));
				customer.setCustSource(rs.getString(4));
				customer.setCustPhone(rs.getString(5));
				customer.setCustMobile(rs.getString(6));
				
				customers.add(customer);
			}

			// 关闭资源
			JDBCutil.closeAll(rs, ps, connection);
			return customers;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<Customer> getCustomerls(int custlevel, String custsource) {
		try {
			String str = "'%"+custsource+"%'";
			String sql = "SELECT cid,custName,dname,custSource," + " custPhone,custMobile from t_customer"
					+ " INNER JOIN t_dict on custLevel=did"+ " where custSource like "+str+"and custLevel=?";
			Connection connection = null;
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(connection, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);
			
			ps.setInt(1, custlevel);
			ResultSet rs = ps.executeQuery();
			List<Customer> customers = new ArrayList<>();
			
			while (rs.next()) {
				Customer customer = new Customer();
				customer.setCid(rs.getInt(1));
				customer.setCustName(rs.getString(2));
				customer.getDict().setDname(rs.getString(3));
				customer.setCustSource(rs.getString(4));
				customer.setCustPhone(rs.getString(5));
				customer.setCustMobile(rs.getString(6));
				
				customers.add(customer);
			}

			// 关闭资源
			JDBCutil.closeAll(rs, ps, connection);
			return customers;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<Customer> getCustomernls(String custname, int custlevel, String custsource) {
		try {
			String str1 = "'%"+custsource+"%'";
			String str = "'%"+custname+"%'";
			String sql = "SELECT cid,custName,dname,custSource," + " custPhone,custMobile from t_customer"
					+ " INNER JOIN t_dict on custLevel=did"+ " where custSource like "+str1+"and custLevel=? and custName like "+str;
			Connection connection = null;
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(connection, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);
			
			ps.setInt(1, custlevel);
			ResultSet rs = ps.executeQuery();
			List<Customer> customers = new ArrayList<>();
			
			while (rs.next()) {
				Customer customer = new Customer();
				customer.setCid(rs.getInt(1));
				customer.setCustName(rs.getString(2));
				customer.getDict().setDname(rs.getString(3));
				customer.setCustSource(rs.getString(4));
				customer.setCustPhone(rs.getString(5));
				customer.setCustMobile(rs.getString(6));
				
				customers.add(customer);
			}

			// 关闭资源
			JDBCutil.closeAll(rs, ps, connection);
			return customers;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<Customer> getCustomernll(int custlevel) {
		try {
			
			String sql = "SELECT cid,custName,dname,custSource,custPhone,custMobile from t_customer,t_dict where custLevel=did and custLevel=?";
			
			Connection connection = null;
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(connection, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);
			
			ps.setInt(1, custlevel);
			
			ResultSet rs = ps.executeQuery();
			List<Customer> customers = new ArrayList<>();
			
			while (rs.next()) {
				Customer customer = new Customer();
				customer.setCid(rs.getInt(1));
				customer.setCustName(rs.getString(2));
				customer.getDict().setDname(rs.getString(3));
				customer.setCustSource(rs.getString(4));
				customer.setCustPhone(rs.getString(5));
				customer.setCustMobile(rs.getString(6));
				
				customers.add(customer);
			}

			// 关闭资源
			JDBCutil.closeAll(rs, ps, connection);
			return customers;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<CustomerEx> getCS() {
		try {
				String sql = "select custSource,count(custSource) from t_customer GROUP BY custSource";
				
				Connection connection = null;
				Properties properties = new Properties();
				InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
				properties.load(in);
				PreparedStatement ps = JDBCutil.getPS(connection, properties.getProperty("jdbc.url"),
						properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);
				
				
				ResultSet rs = ps.executeQuery();
				List<CustomerEx> customerExs = new ArrayList<>();
				
				while (rs.next()) {
					CustomerEx customerEx = new CustomerEx();
					customerEx.setDname(rs.getString(1));
					customerEx.setNum(rs.getInt(2));
					
					customerExs.add(customerEx);
				}
		
				// 关闭资源
				JDBCutil.closeAll(rs, ps, connection);
				return customerExs;
			} 
		catch (Exception e) {
				e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<CustomerEx> getCP() {
		try {
			String sql = "select dname,count(custLevel) from t_customer,t_dict where custLevel=did GROUP BY custLevel";
			
			Connection connection = null;
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(connection, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);
			
			
			ResultSet rs = ps.executeQuery();
			List<CustomerEx> customerExs = new ArrayList<>();
			
			while (rs.next()) {
				CustomerEx customerEx = new CustomerEx();
				customerEx.setDname(rs.getString(1));
				customerEx.setNum(rs.getInt(2));
				
				customerExs.add(customerEx);
			}
	
			// 关闭资源
			JDBCutil.closeAll(rs, ps, connection);
			return customerExs;
		} 
	catch (Exception e) {
			e.printStackTrace();
	}
	return null;
	}

}
